package util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import bean.Customer;
import bean.GoodDetails;
import bean.User;

public class ExcelUtil {
	
	/**
	 * 解析Excel
	 * @param file
	 * @return
	 */
	public static List<Customer> parseExcel(File f) {
		List<Customer> list = new ArrayList<Customer>();
		
		try {
			InputStream stream = new FileInputStream(f);
			Workbook wb = null;
			// 读取第一章表格内容 
			if (f.getAbsolutePath().endsWith("xls")) {
				wb = new HSSFWorkbook(stream);
			} else if (f.getAbsolutePath().endsWith("xlsx")) {
				wb = new XSSFWorkbook(stream);
			} else {
				System.out.println("您输入的excel格式不正确");
			}
			Sheet sh = (Sheet) wb.getSheetAt(0);
			//读取第3行内容
			Row title = sh.getRow(2);
			//匹配标题行
			Map<String,Integer> map = matchTitle(title);
			//读取内容从第4行开始
			//读取内容
			int rowNum = sh.getLastRowNum();
			Customer customer = new Customer();
			List<GoodDetails> goodList = new ArrayList<GoodDetails>();
			List<HashMap<String, String>> khxx = khxxUtil.getMsg();
			int step = 0;
			for (int i = 3; i <= rowNum; i++) {
				Row row = sh.getRow(i);
				if(row == null){
					continue;
				}
				//获取第一列数据
				String firstCell = "";
				if(row.getCell(map.get("类型"))!=null){
					firstCell =row.getCell(map.get("类型")).getStringCellValue();
				}
				if(firstCell.equals("")||firstCell.equals("合计")){//空行，合计跳过
					continue;
				}else if(firstCell.equals("小计")){//读取总金额
					if(customer.getName()!=null){
						//计算总金额和总折扣金额
						Map<String,BigDecimal> monMap = sumAmount(goodList);
						customer.setSumAmount(monMap.get("amount").toString());
						customer.setDisCount(monMap.get("disAmount").toString());
						customer.setGoodDetails(goodList);
						list.add(customer);
						//清空customer
						customer = new Customer();
						//清空goodList
						goodList = new ArrayList<GoodDetails>();
						//清空行号标志
						step = 0;
						continue;
					}
				}else if(!firstCell.equals("")&&row.getCell(map.get("客户名称")).getStringCellValue().equals("")){//客户名称行
					String[] split = firstCell.split(" ");
					String nsrbh = "";
					String billCode = sh.getRow(i+1).getCell(map.get("单据编号")).getStringCellValue();
					customer.setBillCode(billCode);
					if(split.length == 1){
						customer.setBz("");
						customer.setName(split[0]);
						nsrbh = updateSh(split[0],khxx);
					}else{
						customer.setBz(split[0]);
						customer.setName(split[1]);
						nsrbh = updateSh(split[1],khxx);
					}
					customer.setSh(nsrbh);
					continue;
				}else if(firstCell.equals("销售开单")||firstCell.equals("销售退货")){//商品明细行
					if(customer.getName() != null){
						GoodDetails good = new GoodDetails();
						step++;
						good.setXh(step+"");
						good.setCustomer(row.getCell(map.get("客户名称")).getStringCellValue());
						good.setGoodCode(row.getCell(map.get("货品编码")).getStringCellValue());
						good.setGoodName(row.getCell(map.get("货品名称")).getStringCellValue());
						good.setStandard(row.getCell(map.get("规格")).getStringCellValue());
						String dis = getCellFormatValue(row.getCell(map.get("折扣金额")));
						if("".equals(dis.trim())||dis==null){
							dis = "0";
						}
						good.setDiscount(dis);
						String temp = good.getStandard().split("\\*")[0];
						//根据规格型号转换单位
						String[] str = change(temp);
						good.setDw(str[1]);
						String num = getCellFormatValue(row.getCell(map.get("业务单位数量")));
						if(str[1].equals("升")||str[1].equals("公斤")){
							num = new BigDecimal(num).multiply(new BigDecimal(str[0])).divide(new BigDecimal(1000)).toString();
						}
						good.setPrice(new BigDecimal(getCellFormatValue(row.getCell(map.get("销售成本(本)")))).divide(new BigDecimal(num), 8, BigDecimal.ROUND_HALF_UP).toString());
						good.setNum(num);
						good.setAmount(getCellFormatValue(row.getCell(map.get("销售成本(本)"))));
						//存在促销送样商品
						if(row.getCell(map.get("摘要")).getStringCellValue().indexOf("促销送样") != -1 || good.getAmount().equals("0.0")){
							good.setIsPromotion(true);
							customer.setIsPromotion(true);
							
						}
						goodList.add(good);
					}
				}
			}
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (Exception e){
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * 计算总金额和总折扣金额
	 * @param goodList
	 * @return
	 */
	private static Map<String, BigDecimal> sumAmount(List<GoodDetails> goodList) {
		Map<String,BigDecimal> map = new HashMap<>();
		BigDecimal amount = new BigDecimal(0);
		BigDecimal disAmount = new BigDecimal(0);
		for (GoodDetails goodDetails : goodList) {
			amount = amount.add(new BigDecimal(goodDetails.getAmount()));
			disAmount = disAmount.add(new BigDecimal(goodDetails.getDiscount()));
		}
		map.put("amount", amount);
		map.put("disAmount", disAmount);
		return map;
	}

	/**
	 * 匹配标题栏
	 * @param title
	 * @return
	 */
	private static Map<String, Integer> matchTitle(Row title) {
		short cellNum = title.getLastCellNum();
		Map<String,Integer> map = new HashMap<String,Integer>();
		for (int i = 0; i < cellNum; i++) {
			map.put(getCellFormatValue(title.getCell(i)), i);
		}
		return map;
	}

	/**
	 * 修改规格
	 * @param temp
	 * @return
	 */
	private static String[] change(String temp) {
		String[] str = new String[2];
        Pattern pattern = Pattern.compile("(\\d)+|(\\D)+");
        Matcher matcher = pattern.matcher(temp);
        matcher.find();
        str[0]=matcher.group();   
        matcher.find();
        str[1]=matcher.group();
        switch (str[1]) {
		case "ml":
			str[1] = "升";
			break;
		case "g":
			str[1] = "公斤";
			break;
		case "L":
			str[1] = "台";
			break;
		default:
			break;
		}
		return str;
	}

	/**
	 * 根据公司名称查找税号
	 * @param firstCell
	 * @param khxx
	 * @return
	 */
    public static String updateSh(String name, List<HashMap<String, String>> khxx) {
		for(int i=0;i<khxx.size();i++){
			if(khxx.get(i).get("GFMC").equals(name)){
				return khxx.get(i).get("NSRSBH");
			}
		}
    	return null;
	}


	/**
     * 根据HSSFCell类型设置数据
     * @param cell
     * @return
     */
    private static String getCellFormatValue(Cell cell) {
        String cellvalue = "";
        if (cell != null) {
            // 判断当前Cell的Type
            switch (cell.getCellType()) {
            // 如果当前Cell的Type为NUMERIC
            case HSSFCell.CELL_TYPE_NUMERIC:
            case HSSFCell.CELL_TYPE_FORMULA: {
                // 判断当前的cell是否为Date
                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                    // 如果是Date类型则，转化为Data格式
                    
                    //方法1：这样子的data格式是带时分秒的：2011-10-12 0:00:00
                    //cellvalue = cell.getDateCellValue().toLocaleString();
                    
                    //方法2：这样子的data格式是不带带时分秒的：2011-10-12
                    Date date = cell.getDateCellValue();
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    cellvalue = sdf.format(date);
                    
                }
                // 如果是纯数字
                else {
                    // 取得当前Cell的数值
                    cellvalue = String.valueOf(cell.getNumericCellValue());
                }
                break;
            }
            // 如果当前Cell的Type为STRIN
            case HSSFCell.CELL_TYPE_STRING:
                // 取得当前的Cell字符串
                cellvalue = cell.getRichStringCellValue().getString();
                break;
            // 默认的Cell值
            default:
                cellvalue = " ";
            }
        } else {
            cellvalue = "";
        }
        return cellvalue;

    }

    /**
     * 生成冲红Excel
     * @param fsDetail
     * @return
     */
	public static String writeRedExcel(List<GoodDetails> fsDetail) {
		String excelFileName = "冲红明细/冲红数据"+DateUtil.getNow("yyyyMMddHHmmss")+".xls";
		String sheetName = "Sheet1";// name of sheet
        // 第一步，创建一个webbook，对应一个Excel文件  
        HSSFWorkbook wb = new HSSFWorkbook();  
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet  
        HSSFSheet sheet = wb.createSheet(sheetName);  

		CellStyle cellStyle = wb.createCellStyle();
		cellStyle.setWrapText(true);
		HSSFFont font = wb.createFont(); 
		font.setFontHeightInPoints((short)14);
		cellStyle.setFont(font);
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		//设置第一行
		HSSFRow row = sheet.createRow(0);
		String[] title = new String[]{"类型","货品编码","货品名称","规格","单位","数量","单价","金额","客户名称"}; 
		for (int i = 0; i < title.length; i++) {
			Cell cell = row.createCell(i);
			cell.setCellValue(title[i]);
		}
		//设置内容
		for (int i = 0; i < fsDetail.size(); i++) {
			HSSFRow detailRow = sheet.createRow(i+1);
			detailRow.createCell(0).setCellValue("销售退货");
			detailRow.createCell(1).setCellValue(fsDetail.get(i).getGoodCode());
			detailRow.createCell(2).setCellValue(fsDetail.get(i).getGoodName());
			detailRow.createCell(3).setCellValue(fsDetail.get(i).getStandard());
			detailRow.createCell(4).setCellValue(fsDetail.get(i).getDw());
			detailRow.createCell(5).setCellValue(fsDetail.get(i).getNum());
			detailRow.createCell(6).setCellValue(fsDetail.get(i).getPrice());
			detailRow.createCell(7).setCellValue(fsDetail.get(i).getAmount());
			detailRow.createCell(8).setCellValue(fsDetail.get(i).getCustomer());
		}	
		
		File f = new File(excelFileName);
		try {
			if(!f.getParentFile().exists()){
				f.getParentFile().mkdirs();
			}
			if (!f.exists()) {
				f.createNewFile();
			}
			FileOutputStream fileOut = new FileOutputStream(excelFileName);
			wb.write(fileOut);
			fileOut.flush();
			fileOut.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return f.getAbsolutePath();
	}
	
	/**
	 * 导出勾选数据
	 * @param selectMap
	 * @return
	 */
	public static Boolean exportData(Map<String, Customer> selectMap) {
		String excelFileName = "导出Excel/导出数据"+DateUtil.getNow("yyyyMMddHHmmss")+".xls";
		String sheetName = "Sheet1";// name of sheet
        // 第一步，创建一个webbook，对应一个Excel文件  
        HSSFWorkbook wb = new HSSFWorkbook();  
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet  
        HSSFSheet sheet = wb.createSheet(sheetName);  

		CellStyle cellStyle = wb.createCellStyle();
		cellStyle.setWrapText(true);
		HSSFFont font = wb.createFont(); 
		font.setFontHeightInPoints((short)14);
		cellStyle.setFont(font);
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
		
		//设置第一行
		HSSFRow row1 = sheet.createRow(0);
		row1.createCell(0).setCellValue("数据导出");
		//设置第二行
		HSSFRow row2 = sheet.createRow(1);
		row2.createCell(0).setCellValue("时间："+DateUtil.getNow("yyyy-MM-dd"));
		//设置第三行标题行
		HSSFRow row3 = sheet.createRow(2);
		String[] title = new String[]{"类型","单据编号","货品编码","货品名称","规格","单位","业务单位数量","单价","金额","摘要","客户名称"}; 
		for (int i = 0; i < title.length; i++) {
			Cell cell = row3.createCell(i);
			cell.setCellValue(title[i]);
		}
		//设置内容
		int step = 3;
		for (String key : selectMap.keySet()) {
			Customer customer = selectMap.get(key);
			//客户名称行
			HSSFRow nameRow = sheet.createRow(step);
			nameRow.createCell(0).setCellValue(customer.getGoodDetails().get(0).getCustomer());
			nameRow.createCell(10).setCellValue("");
			//商品行
			List<GoodDetails> fsDetail = customer.getGoodDetails();
			BigDecimal total = new BigDecimal(0);
			for (int i = 0; i < fsDetail.size(); i++) {
				step++;
				HSSFRow detailRow = sheet.createRow(step);
				detailRow.createCell(0).setCellValue("销售开单");
				detailRow.createCell(1).setCellValue(customer.getBillCode());
				detailRow.createCell(2).setCellValue(fsDetail.get(i).getGoodCode());
				detailRow.createCell(3).setCellValue(fsDetail.get(i).getGoodName());
				detailRow.createCell(4).setCellValue(fsDetail.get(i).getStandard());
				detailRow.createCell(5).setCellValue(fsDetail.get(i).getDw());
				detailRow.createCell(6).setCellValue(fsDetail.get(i).getNum());
				detailRow.createCell(7).setCellValue(fsDetail.get(i).getPrice());
				detailRow.createCell(8).setCellValue(Double.parseDouble(fsDetail.get(i).getAmount()));
				detailRow.createCell(9).setCellValue("");
				detailRow.createCell(10).setCellValue(fsDetail.get(i).getCustomer());
				total = total.add(new BigDecimal(fsDetail.get(i).getAmount()));
			}
			//小计行
			step++;
			HSSFRow amountRow = sheet.createRow(step);
			amountRow.createCell(0).setCellValue("小计");
			amountRow.createCell(8).setCellValue(total.doubleValue());
			amountRow.createCell(9).setCellValue(Double.parseDouble(customer.getSumAmount()));
			//空行
			step++;
			HSSFRow nullRow = sheet.createRow(step);
			nullRow.createCell(0).setCellValue("");
			step++;
		}
		
		File f = new File(excelFileName);
		try {
			if(!f.getParentFile().exists()){
				f.getParentFile().mkdirs();
			}
			if (!f.exists()) {
				f.createNewFile();
			}
			FileOutputStream fileOut = new FileOutputStream(excelFileName);
			wb.write(fileOut);
			fileOut.flush();
			fileOut.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return true;
	}
	
	/**
	 * 导出客户信息excel
	 * @param list
	 * @throws IOException
	 */
	public static Boolean outputExcel(List<User> list,File file) {
		try{
			String excelFileName = file.getPath()+"/khxx.xls";
		
			String sheetName = "Sheet1";
				
		        HSSFWorkbook wb = new HSSFWorkbook();  
		 
		        HSSFSheet sheet = wb.createSheet(sheetName);  
		
				CellStyle cellStyle = wb.createCellStyle();
				
				cellStyle.setWrapText(true);
				
				cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
			HSSFRow row = sheet.createRow(0);
			row.createCell(0).setCellValue("客户名称");
			row.createCell(1).setCellValue("税号");
			row.createCell(2).setCellValue("联系人");
			row.createCell(3).setCellValue("地址电话");
			row.createCell(4).setCellValue("银行账号");
			row.createCell(5).setCellValue("备注");
			row.createCell(6).setCellValue("区域");
			for (int r = 0; r < list.size(); r++) {
						row = sheet.createRow(r+1);
						User user = list.get(r);
						row.createCell(0).setCellValue(user.getName());
						row.createCell(1).setCellValue(user.getSh());
						row.createCell(2).setCellValue(user.getLinkman());
						row.createCell(3).setCellValue(user.getDzdh());
						row.createCell(4).setCellValue(user.getYhzh());
						row.createCell(5).setCellValue(user.getBz());
						row.createCell(6).setCellValue(user.getArea());
			}
		
			File f = new File(excelFileName);
			if(!f.getParentFile().exists()){
				f.getParentFile().mkdirs();
			}
			if (!f.exists()) {
				f.createNewFile();
			}
			FileOutputStream fileOut = new FileOutputStream(excelFileName);
			wb.write(fileOut);
			fileOut.flush();
			fileOut.close();
			return true;
		} catch(Exception e) {
			e.printStackTrace();
			return false;
		}
	}
}
